package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modellayer.PartOrder;
import modellayer.Product;

public class PartOrderDB
{
	private Connection con;
	
	public PartOrderDB()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	public ArrayList<PartOrder> getPartOrdersToOrder(int orderNo) {
		String wClause = " orderNo='"+orderNo+"'";
		
		return miscWhere(wClause);
	}
	
	public int insertPartOrder(PartOrder partOrderObj) throws Exception
	{
		int rc = -1;
		
		String query = "INSERT INTO PartOrder (amount, qty, productNo) VALUES ('"+partOrderObj.getAmount()+"', '"+partOrderObj.getQty()+"', '"+partOrderObj.getProductObj().getProductNo()+"');";
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch (SQLException ex)
		{
			System.out.println("SQL query" +query);
			System.out.println("Error: " +ex);
		}
		
		return rc;
	}
	
	public int updatePartOrder(int partOrderNo, double amount, int qty)
	{
		int rc = -1;
		
		String query = "UPDATE PartOrder " + "SET amount='"+amount+"', qty='"+qty+"' " + "WHERE PartOrderNo='"+partOrderNo+"'";
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch (SQLException ex)
		{
			System.out.println("SQL query" +query);
			System.out.println("Error: " +ex);
		}
		
		return rc;
	}
	
	public void deletePartOrder(int partOrderNo)
	{
		String query = "DELETE FROM PartOrder WHERE PartOrderNo='"+partOrderNo+"'";
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
		}
		catch (SQLException ex)
		{
			System.out.println("SQL query" +query);
			System.out.println("Error: " +ex);
		}
	}
	
	public PartOrder findPartOrder(int partOrderNo)
	{
		String wClause = " PartOrderNo='"+partOrderNo+"'";
		
		return singleWhere(wClause);
	}
	
	private PartOrder singleWhere(String wClause)
	{
		ResultSet results;
		PartOrder partOrderObj = null;
		
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				partOrderObj = buildPartOrderObj(results);
			}
			
			stmt.close();
		}
		catch(Exception ex)
		{
			System.out.println("SQL query " +query);
			System.out.println("Error " +ex);
		}
		
		return partOrderObj;
	}
	
	private ArrayList<PartOrder> miscWhere(String wClause)
	{
        ResultSet results;
	    ArrayList<PartOrder> list = new ArrayList<PartOrder>();	
		
	    String query =  buildQuery(wClause);
  
            try{ 
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
			PartOrder partOrderObj = buildPartOrderObj(results);	
                 list.add(partOrderObj);
         		
		}
        
		stmt.close();     
                 
		}
	 	catch(Exception e){
	 		System.out.println("miscWhere PartOrder Error: " + e);
	 	}
		return list;
	}
	
	private PartOrder buildPartOrderObj(ResultSet results)
	{
		PartOrder partOrderObj = null;
		
		try
		{
			partOrderObj = new PartOrder(results.getInt("partOrderNo"), results.getDouble("amount"), results.getInt("qty"), new Product(results.getInt("productNo")));
		}
		catch(SQLException e)
		{
			System.out.println("Error building partOrderObject! \nError: " +e);
		}
		
		return partOrderObj;
	}
	
	private String buildQuery(String wClause)
	{
		String query = "SELECT partOrderNo, amount, qty, productNo FROM PartOrder";
		
		if (!wClause.isEmpty())
		{
			query += " WHERE " +wClause;
		}
		
		return query;
	}
}